package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.PlantsVersionsMonths;

public class PlantsVersionsMonthsDao extends DBDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;
	
	/**SQL Select all cost elements*/
	protected static final String SQL_SELECT_ALL = "SELECT pv.plant_version_id, pv.plant_id, p.short_name plant_short_name, p.description plant_description,  "
															 + "		mpv.month_period_version_id, mp.month_period_id, mp.short_name month_short_name, mp.description month_description,  "
															 + "		mp.month, mp.year, mpv.version_id "
															 + "	FROM plants_versions pv, plants p, months_periods_versions mpv, months_periods mp "
															 + "	WHERE pv.plant_id = p.plant_id "
															 + "		AND pv.month_period_version_id = mpv.month_period_version_id "
															 + "		AND mpv.month_period_id = mp.month_period_id ";
	
	protected static final String SQL_SELECT_ALL_ORDER_BY = " mp.month_period_id, mpv.month_period_version_id, pv.plant_version_id";
	
	public ArrayList getArray(int versionId, int plantId) throws Exception {
		StringBuffer sql 			= null;
		PlantsVersionsMonths dto	= null;
		ArrayList array				= null;
		PreparedStatement pstm		= null;
		ResultSet rs 				= null;
		int paramPos				= 1;
		
		try{
			array = new ArrayList(24);
			
			//la versionId viene integrada en el filters
			
			sql = new StringBuffer(SQL_SELECT_ALL);
			
			if (versionId > 0)
				sql.append(" AND mpv.version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
			
			if (plantId > 0)
				sql.append(" AND pv.plant_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
			
			sql.append(DBDAO.SQL_ORDER_BY);
			sql.append(SQL_SELECT_ALL_ORDER_BY);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			if (versionId > 0)
				pstm.setInt(paramPos++, versionId);
			
			if (plantId > 0)
				pstm.setInt(paramPos++, plantId);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new PlantsVersionsMonths();
				
				dto.setMonth(rs.getInt("month"));

				dto.setMonthDescription(rs.getString("month_description"));
				dto.setMonthPeriodId(rs.getInt("month_period_id"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setMonthShortName(rs.getString("month_short_name"));
				
				dto.setPlantDescription(rs.getString("plant_description"));
				dto.setPlantId(rs.getInt("plant_id"));
				dto.setPlantShortName(rs.getString("plant_short_name"));
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				
				dto.setVersionId(rs.getInt("version_id"));
				
				dto.setYear(rs.getInt("year"));

				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}
}
